Forum Discussion

Dhrutivyasa-070's avatar
Dhrutivyasa-070
Helper I
3 years ago
Solved

DAX

Hello Community,  I have one Taste Rating dataset which contains following column. I want to compare one primary taster rating with other taster. and i want to slicer for taster name. One is for pr...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Dhrutivyasa-070 
    Please refer to attached sample file with the proposed solution.

    Number of Lot Tasted = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            VALUES ( 'Table'[UniqueID] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
    Match Lot = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            SUMMARIZE ( 'Table', 'Table'[UniqueID], 'Table'[TastingScore] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
    % Match = DIVIDE ( [Match Lot], [Number of Lot Tasted] )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Dhrutivyasa-070 
Please refer to attached sample file with the proposed solution.

Number of Lot Tasted = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            VALUES ( 'Table'[UniqueID] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
Match Lot = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            SUMMARIZE ( 'Table', 'Table'[UniqueID], 'Table'[TastingScore] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
% Match = DIVIDE ( [Match Lot], [Number of Lot Tasted] )
  • Dhrutivyasa-070's avatar
    Dhrutivyasa-070
    Helper I
    3 years ago

    Thank You Very Much @tamrej1
    Its working Properly in my Dataset.

  • Dhrutivyasa-070's avatar
    Dhrutivyasa-070
    Helper I
    3 years ago

    Hello tamerj1,
    i have facing one more problem.

    in my data set another column add which is rating category. Rating score are categories for increasing match percetage.

    UniqueIDTasterScoreCategory
    100x20a
    100y22b
    100z20a
    200x23b
    200y23b
    300x24a
    300z26b
    400x30a
    400z31a

     

     

    My Primary Taster is X and Secondary Taster is Z nd Catogory A selected in Slicer.

    Required Output :

    Number of Lot Tasted : 3 (Number of Lot both tasted nd primary taster category =a)

    Match : 2.

    All Comparision based on Primary Taster.

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Dhrutivyasa-070 
      I don't fully understand the requiremnt.

      Shall the number of lot tasted ignore the category?

      How did you calculate match = 2?