Forum Discussion

madhav2020's avatar
madhav2020
Frequent Visitor
5 years ago
Solved

Dax query to count unique values in a column by comparing it with other column

Hey folks,

 

Need your help with a query.

 

Here goes the problem definition,

 

Below table has ID column with unquie values and the corresponding Risk factor column which has same/different values.

 

Now expected output will be to check if values in Risk Factor column is changing wrt ID and if does then expected ouput will be 1 and if not then 0 as shown below.

Risk                   OIDExpected output
11.021445.34552.17462.60490        1
75.321445.34552.17462.60490 
73.621445.34552.17462.60490 
72.521445.34552.17486.61667         0
11.021445.34552.17510.33157         0
11.021445.34552.17510.33157 
0.021445.34552.17511.36651         0
0.021445.34552.17511.36651 
0.021445.34552.17511.36651 
0.021445.34552.17511.36651 
0.021445.34552.17511.36651 
0.021445.34552.17511.36651 
0.021445.34552.17511.36651 
0.021445.34552.17511.36651 
81.121445.34552.17558.59465            1 
76.421445.34552.17558.59465 
0.021445.34552.17608.42491            0
0.021445.34552.17608.42491 
0.021445.34552.17608.42491 
100.021445.34552.17637.32864             0
100.021445.34552.17637.32864 
42.321445.34552.17689.35090              0
General Comment
Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    Hi madhav2020 

    New column =
IF (
    CALCULATE ( DISTINCTCOUNT ( Table1[Risk] ), ALLEXCEPT ( Table1, Table1[OID] ) ) > 1,
    1,
    0
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

     

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi madhav2020 

    New column =
IF (
    CALCULATE ( DISTINCTCOUNT ( Table1[Risk] ), ALLEXCEPT ( Table1, Table1[OID] ) ) > 1,
    1,
    0
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers