DAX Measure Switch with Hasonevalue gives incorrect results

[Invesco]

Hi Experts

I have the following DAX measure which is giving the incorrect results when used as a measure in standard table

However, if i added the measures for example measure 6. TargetCoverage% to a card visual the end result is correct.

My discount table is as follows:

FieldActivity_Total = 
VAR metric =
    SELECTEDVALUE ( 'Map_Field_Activity'[Sort] )
RETURN
IF(HASONEVALUE('Map_Field_Activity'[Metrics]),
              SWITCH (
                metric
                ,1,FORMAT ( [ActiveReps], "#,#" )
                ,2,FORMAT ( [Vacancy%]+0, "0.0%" )
                ,3,[CallDayRep]  --FORMAT ( [CallDayRep], "#,#")
                ,4,FORMAT ( [EffectiveInteractions %], "0.0%" )
                ,5,FORMAT ( [HCPsInPlanCoverageProduct], "#,#" )
                ,6,FORMAT ( [TargetCoverage%], "0.0%" )
                ,7,FORMAT ( [F2FCalls], "#,#" )
                ,8,FORMAT ( [VisitFreq], "#,#.##" )
                ,9,FORMAT ( [%Call Product], "0.0%" )
                ,10,FORMAT ([%useOfADV], "0.0%" )
                ,11,FORMAT ( [Events], "#,#" )
                ,12,FORMAT ( [PartRP], "#,#" )
                ,BLANK()
              ))

[Anonymous · Accepted solution]

Hi @Invesco ,

I'm guessing you're trying to change the format via sort, but that doesn't seem right with your current expression. First you fetch the value, which is correct, then you determine if the slicer fetched the value, and finally you make a judgment on the fetched value. So you should first have sort as a separate dimension table, which you can see below:

So you can try:

DimSort = SUMMARIZE('Map_Field_Activity','Map_Field_Activity'[Sort])
FieldActivity_Total = 
VAR _select =
    SELECTEDVALUE ( 'DimSort'[Sort] )
RETURN
IF(HASONEVALUE('DimSort'[Sort]),
              SWITCH (
                _select
                ,1,FORMAT ( [ActiveReps], "#,#" )
                ,2,FORMAT ( [Vacancy%]+0, "0.0%" )
                ,3,[CallDayRep]  --FORMAT ( [CallDayRep], "#,#")
                ,4,FORMAT ( [EffectiveInteractions %], "0.0%" )
                ,5,FORMAT ( [HCPsInPlanCoverageProduct], "#,#" )
                ,6,FORMAT ( [TargetCoverage%], "0.0%" )
                ,7,FORMAT ( [F2FCalls], "#,#" )
                ,8,FORMAT ( [VisitFreq], "#,#.##" )
                ,9,FORMAT ( [%Call Product], "0.0%" )
                ,10,FORMAT ([%useOfADV], "0.0%" )
                ,11,FORMAT ( [Events], "#,#" )
                ,12,FORMAT ( [PartRP], "#,#" )
                ,BLANK()
              ))

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[DataInsights]

@Invesco,

Field parameters provide a more robust way of achieving this. One of the benefits is that you don't have to use the FORMAT function which converts the value to text. Instead, each measure's formatting is used.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters