Forum Discussion

Agent47's avatar
Agent47
Helper I
1 year ago
Solved

Different measures returning same value

I have written the following measures for corresponding TAbleau Calculated functions  ---------------------------- { INCLUDE [Risk] : COUNTD([Ra Code])} (Tableau) ---- Measure_RA_SUM =     CAL...
General Comment
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

     

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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's avatar
Agent47
Helper I
1 year ago

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 .