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 =
    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 

 

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.

     

5 Replies

  • 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 .