Forum Discussion

animebuff's avatar
animebuff
Helper I
1 year ago
Solved

DAX issue

requirement:   left side visual is what by default we get in PBI what I need is right side visual but the values of all region should not be 0.31 rather apac - 1 europe - 0.5 la - 0.5 af...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi wini_R 

     

    Please try this measure:

    MEASURE =
VAR _currentRegion =
    SELECTEDVALUE ( 's rate'[region] )
VAR _ifdenominator =
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 's rate' ),
                's rate'[region] = _currentRegion
                    && 's rate'[inclusion flag] = "n"
            ),
            's rate'[mk],
            's rate'[region]
        )
    )
VAR _outcome =
    CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
        / COUNT ( 'S rate'[success] )
RETURN
    IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi animebuff 

 

Here are 2 workarounds:

1:

Use the default dax:

Measure = 
CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
    / COUNT ( 'S rate'[success] )

Then right-click the [region] field in the X-axis and select Show items with no data:

The result is as follow:

 

2:

Change the measure as the follow:

Measure 2 = 
VAR _AAA = CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
    / COUNT ( 'S rate'[success] )
RETURN 
IF(_AAA<>0,_AAA,0)

The result is as follow:

 

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • animebuff's avatar
    animebuff
    Helper I
    1 year ago

    both methods will show data as 0 for months which doesn't have date surrogate key in fact table

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi wini_R 

       

      Please try this measure:

      MEASURE =
VAR _currentRegion =
    SELECTEDVALUE ( 's rate'[region] )
VAR _ifdenominator =
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 's rate' ),
                's rate'[region] = _currentRegion
                    && 's rate'[inclusion flag] = "n"
            ),
            's rate'[mk],
            's rate'[region]
        )
    )
VAR _outcome =
    CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
        / COUNT ( 'S rate'[success] )
RETURN
    IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )

      The result is as follow:

       

       

      Best Regards

      Zhengdong Xu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • animebuff's avatar
        animebuff
        Helper I
        1 year ago

        thanks Anonymous 

        your solution was perfect

        but now I face 2 problem in my offical PBI file,

        1. my interaction is not happening between year ( also month ) slicer with this metric

        we have 4 years ( 2021,2022, 2023 and 2024) 
        whatever year I'm selecting I'm getting all the years

        relationship between fact & dimension table is fine

         

        2. for ex. even if fact table not having date surrogate key for month year sep 21 

        I still data for sep 21 in the metric,

        sep 21 shows 1 or 100% and all region inside sep 21 also shows 1 or 100%