Forum Discussion

trdoan's avatar
trdoan
Helper III
7 years ago
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 relationship through column "Store".

 

I have:

  • 2 Card visuals to count distinct the number of Stores in 'NDT' & 'Laboratory'
  • a "StoreName" slicer created from 'General Info' [Store]

Question 1:

Is there a way to COUNT DISTINCT the number of Stores and RETURN "0" in those 2 Cards if the selection made from the StoreName slicer does not exist in 'NDT' or 'Laboratory'?

 

Ex: If Z is selected from StoreName slicer, the Count result should return 0 instead of  the default (Blank)

 

 

Question 2:

a. If T is selected from the StoreName slicer and  Ultrasonic is selected from the NDT slicer, is it possible to have Power BI showed "No alternate Store can do Ultrasonic"

b. If H is selected from the StoreName slicer and STI is selected from the NDT slicer, it it possible to show:

  • Count of alternatives to the selected Store = 2
  • List of alternate Stores = E ; T

 

Can anyone please show me how to tackle this? Thank you so much!

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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

    • trdoan's avatar
      trdoan
      Helper III
      7 years ago

      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!

      • MFelix's avatar
        MFelix
        Super User
        7 years ago
        What version are you on?

        I can try and make a file in your version.

        Regards,
        MFelix