correct P BI measure: P_ No of Doc. Types (Unidentified) = DISTINCTCOUNT('Cozmos…
- User
correct P BI measure: P_ No of Doc. Types (Unidentified) = DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]) && ('Cozmos ETE'[Document_Type] = "UNIDENTIFIED")
Assistant
Of course. Your current DAX formula has a syntax error. You cannot use the
&&operator to connect a calculation function likeDISTINCTCOUNTwith a filter condition.The correct way to write this measure is by using the
CALCULATEfunction, which modifies the context in which a calculation is performed.Corrected Measure (Recommended)
This is the most efficient and standard way to write this measure in DAX.
P_ No of Unidentified Docs = CALCULATE( DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]), 'Cozmos ETE'[Document_Type] = "UNIDENTIFIED" )Explanation
CALCULATE(<expression>, <filter>): This is the most powerful function in DAX. It evaluates an<expression>(your calculation) within a modified filter context.DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]): This is the expression you want to calculate. It counts the unique transaction IDs.'Cozmos ETE'[Document_Type] = "UNIDENTIFIED": This is the filter argument.CALCULATEapplies this filter to the data model before performing the distinct count. It essentially tells Power BI to "only consider rows where the Document_Type is 'UNIDENTIFIED'".
Alternative (Slightly More Verbose)
You can also achieve the same result by explicitly using the
FILTERfunction insideCALCULATE. This is useful for more complex, multi-condition filtering but is not necessary for this simple case.P_ No of Unidentified Docs = CALCULATE( DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]), FILTER( 'Cozmos ETE', 'Cozmos ETE'[Document_Type] = "UNIDENTIFIED" ) )Best Practice Tip: Measure Naming
Your original measure name
P_ No of Doc. Types (Unidentified)could be slightly misleading. It sounds like you are counting the "types" of documents, which would always be 1 (the type named "UNIDENTIFIED").Based on your formula, you are actually counting the number of transactions that are unidentified. I've suggested a clearer name in the corrected code:
P_ No of Unidentified Docs. You can adjust it to whatever fits your model best.- User
correct: P_ No of Doc. Types (Unidentified) = DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]), ('Cozmos ETE'[Document_Type] = "UNIDENTIFIED" )
Assistant
You've got the right idea! You want to perform a distinct count, but only for rows that meet a specific condition. Your DAX syntax is just a little off.
The correct way to combine a calculation with a filter is by using the
CALCULATEfunction.Corrected DAX Measure
This is the standard and most efficient way to write your measure:
P_ No of Unidentified Docs = CALCULATE( DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]), 'Cozmos ETE'[Document_Type] = "UNIDENTIFIED" )How it Works
CALCULATE(...): This is the most important function in DAX. It modifies the context in which a calculation is performed.DISTINCTCOUNT('Cozmos ETE'[Transaction_ID]): This is your expression. It's the calculation you want to perform (count the unique transaction IDs).'Cozmos ETE'[Document_Type] = "UNIDENTIFIED": This is your filter.CALCULATEapplies this filter to your data model before it runs the distinct count.
In plain English, the formula tells Power BI: "Count the unique Transaction IDs, but only for the rows where the Document_Type is 'UNIDENTIFIED'."
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.