Different measures returning same value

[Agent47]

I have written the following measures for corresponding TAbleau Calculated functions 
----------------------------
{ INCLUDE [Risk] : COUNTD([Ra Code])} (Tableau)
----
Measure_RA_SUM =

    CALCULATE(

        DISTINCTCOUNT('Risk Assesment'[Ra_Code]),

        ALLEXCEPT('Hazard', 'Hazard'[Risk])

    )
---------------
  { INCLUDE [Control Type] : COUNTD([Ra Code])} (Tableau)
----

Measure_RA_SUM2 =

    CALCULATE(

        DISTINCTCOUNT('Risk Assesment'[Ra_Code]),

        ALLEXCEPT('Consequences', 'Consequences'[Control_Type])

    )
----------------------------------
{ INCLUDE[RA Type],[Vessel]  : COUNTD([Ra Code])}(Tableau)
-----

Measure_RA_SUM3 =

    CALCULATE(

        DISTINCTCOUNT('Risk Assesment'[Ra_Code]),

        ALLEXCEPT('Risk Assesment', 'Risk Assesment'[Ra_Type], 'Risk Assesment'[Vessel])

    )
----------------------------------------
COUNTD([Ra Code]) (Tableau)
--

Measure_RA_Code_Cnt = DISTINCTCOUNT('Risk Assesment'[Ra_Code])
---------------------------

RA_code is present in Risk Assessment table and Risk is present in Hazard table . Those two tables are connected by a primary key Ra_Master_Id

But they all return same value 

[rajendraongole1 · Accepted solution]

Hi @Agent47 -  It is returning the same value because the ALLEXCEPT function might not be filtering the way you expect, likely due to the relationships in your data model.

create measure RA_SUM as below:

Measure_RA_SUM =
CALCULATE(
DISTINCTCOUNT('Risk Assesment'[Ra_Code]),
TREATAS(VALUES('Hazard'[Risk]), 'Risk Assesment'[Risk])
)

another measure sum2 

Measure_RA_SUM2 =
CALCULATE(
DISTINCTCOUNT('Risk Assesment'[Ra_Code]),
TREATAS(VALUES('Consequences'[Control_Type]), 'Risk Assesment'[Control_Type])
)

sum3 measure:

Measure_RA_SUM3 =
CALCULATE(
DISTINCTCOUNT('Risk Assesment'[Ra_Code]),
REMOVEFILTERS('Risk Assesment'[RA_Type]),
REMOVEFILTERS('Risk Assesment'[Vessel]),
VALUES('Risk Assesment'[RA_Type]),
VALUES('Risk Assesment'[Vessel])
)

Measure_RA_Code_Cnt (No Change)

Measure_RA_Code_Cnt = DISTINCTCOUNT('Risk Assesment'[Ra_Code])

THorughly check that the relationships are correctly set up, and verify that Ra_Master_Id is properly connecting Hazard and Risk Assesment.

you can also use the Performance Analyzer or DAX Studio to see how filters are propagating across tables to understand any unexpected filtering.

Hope this helps.

[Agent47]

Hi Rajendra , now the values are indeed unique ,thanks for that. 

but the expected values 
are 

I changed Measure_RA_SUM =
CALCULATE(
DISTINCTCOUNT('Risk Assesment'[Ra_Code]),
TREATAS(VALUES('Hazard'[Risk]), 'Risk Assesment'[Risk])
)  
with 

Measure_RA_SUM =

CALCULATE(

DISTINCTCOUNT('Risk Assesment'[Ra_Code]),

TREATAS(VALUES('Hazard'[Risk]), 'Hazard'[Risk])

)
as Risk resides in table 'Hazard' .
similarly 
Measure_RA_SUM2 =
CALCULATE(
DISTINCTCOUNT('Risk Assesment'[Ra_Code]),
TREATAS(VALUES('Consequences'[Control_Type]), 'Risk Assesment'[Control_Type])
)
was replaced by 

Measure_RA_SUM2 =

CALCULATE(

DISTINCTCOUNT('Risk Assesment'[Ra_Code]),

TREATAS(VALUES('Consequences'[Control_Type]), 'Consequences'[Control_Type])

) 
----------

cross filter direction = both in all cases.
Can you please suggest any alternative ? Thanks again .

[Agent47]

Many thanks @rajendraongole1 

[Agent47]

@SamWiseOwl  @Kedar_Pande  @lbendlin  please contribute if you can

[Agent47]

@rajendraongole1  please help