Forum Discussion
Solved
Count Distinct from different tables + Find matching values
Hi everyone, Here is my sample data. The 3 sheets are my 3 tables where the "General Info" table has all Store Names and the other 2 tables have several Store Names which have 1-to-many relat...
Hi trdoan,
Create the following measures:
Distintc Count Laboratory = IF ( DISTINCTCOUNT ( NDT[Store] ) = BLANK (); 0; DISTINCTCOUNT ( Laboratory[Store] ) ) + 0 Distintc Count NDT = IF ( DISTINCTCOUNT ( Laboratory[Store] ) = BLANK (); 0; DISTINCTCOUNT ( NDT[Store] ) ) + 0 NDT Alternatives Count = IF ( CALCULATE ( COUNT ( NDT[NDT] ); ALLSELECTED ( NDT[NDT] ) ) <> 1; DISTINCTCOUNT ( NDT[Store] ) ) NDT No alternatives = IF ( CALCULATE ( COUNT ( NDT[NDT] ); ALLSELECTED ( NDT[NDT] ) ) = 1; "No alternate Store can do : " & CONCATENATEX ( NDT; SELECTEDVALUE ( NDT[NDT] ); "," ); "Alternative Stores: " & CONCATENATEX ( NDT; NDT[Store]; "," ) )Check PBIX file attach.
Regards,
MFelix
Hi trdoan,
Create the following measures:
Distintc Count Laboratory =
IF (
DISTINCTCOUNT ( NDT[Store] ) = BLANK ();
0;
DISTINCTCOUNT ( Laboratory[Store] )
) + 0
Distintc Count NDT =
IF (
DISTINCTCOUNT ( Laboratory[Store] ) = BLANK ();
0;
DISTINCTCOUNT ( NDT[Store] )
) + 0
NDT Alternatives Count =
IF (
CALCULATE ( COUNT ( NDT[NDT] ); ALLSELECTED ( NDT[NDT] ) ) <> 1;
DISTINCTCOUNT ( NDT[Store] )
)
NDT No alternatives =
IF (
CALCULATE ( COUNT ( NDT[NDT] ); ALLSELECTED ( NDT[NDT] ) ) = 1;
"No alternate Store can do : "
& CONCATENATEX ( NDT; SELECTEDVALUE ( NDT[NDT] ); "," );
"Alternative Stores: " & CONCATENATEX ( NDT; NDT[Store]; "," )
)
Check PBIX file attach.
Regards,
MFelix
Hello MFelix many thanks to your help! However, I noticed a few things which I've noted on the file here.
My Power BI version at work isn't up-to-date so I couldn't open your file, therefore I don't know if there're many differences in the problems I found compared to your file.
Please advise! Thank you sooo much!
