Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables related by the Client id column. Table 'Client Table' & 'Status Change Table'.
I need a distinct count of Client id from the 'Client Table'.
I only want to distinct count the Client id under the following condition.
If the Status_ID or the Current_Status_ID columns in the 'Status Change Table' ever contain 5 then do not distinct count Client id in the 'Client Table'.
Desired result shown below.
Solved! Go to Solution.
Hi @Qotsa
Link to download the file: https://gofile.io/d/cVkADd
Try this code to add a column to your Client table:
Result =
VAR _Count =
COUNTROWS (
CALCULATETABLE (
EXCEPT (
VALUES ( Client[Client_ID] ),
SUMMARIZE (
FILTER (
'Status Change',
'Status Change'[Status_ID] = 5
|| 'Status Change'[Current_Status_ID] = 5
),
Client[Client_ID]
)
),
ALLEXCEPT ( Client, Client[Client_ID] )
)
)
RETURN
IF ( ISBLANK ( _Count ), 0, _Count )
Output:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your Kudos !!
Hi @Qotsa
Link to download the file: https://gofile.io/d/cVkADd
Try this code to add a column to your Client table:
Result =
VAR _Count =
COUNTROWS (
CALCULATETABLE (
EXCEPT (
VALUES ( Client[Client_ID] ),
SUMMARIZE (
FILTER (
'Status Change',
'Status Change'[Status_ID] = 5
|| 'Status Change'[Current_Status_ID] = 5
),
Client[Client_ID]
)
),
ALLEXCEPT ( Client, Client[Client_ID] )
)
)
RETURN
IF ( ISBLANK ( _Count ), 0, _Count )
Output:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your Kudos !!
Apologies. Previous PBIX incorrect.
Correct PBIX -> https://www.dropbox.com/s/h4fbc0m2gfg2b86/Client%20Status%20Change.pbix?dl=0
can you share sample data or a PBIX file (through Ondrive, Dropbox...)?
Do you need a measure or calculated column?
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |