Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to create a measure with IF function that the logic is based on a merged column?

Hi Fabric Community,   I would like to create a measure with the IF function, whereby Premium per agent = IF(Data[Merged yyyymmm]="2024 Jan", DIVIDE(SUM(Data[premium amount]),3), DIVIDE(SUM(Data[p...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Thanks for the reply from Gabry , please allow me to provide another insight:

    You can use the SELECTEDVALUE() function to get the current value in the table visual in the report view, this may help you:

    Measure = 
IF (
    SELECTEDVALUE (Data[Merged yyyymmm]) = "2024 Jan",
    DIVIDE ( SUM ( Data[premium amount] ), 3 ),
    DIVIDE (
        SUM ( Data[premium amount] ),
        DISTINCTCOUNTNOBLANK ( Data[Merged Alias] )
    )
)

     

    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.

Gabry's avatar
Gabry
Icon for Super User rankSuper User
2 years ago

Hello,

you don't need to use a merged column with yyyymm. You can use your regular date column with date data type.

 

You can use the function datesbetween...so your formula should look like this

 

test = IF(SELECTEDVALUE('Table'[Date]) in DATESBETWEEN('Table'[Date], DATE(2024,01,01),DATE(2024,01,31)), CALCULATE(DIVIDE(SUM('Table'[number]),3)),DIVIDE(SUM(Data[premium amount]),DISTINCTCOUNTNOBLANK(Data[Merged Alias])