Using variable in order to return values from max

[rsanyoto]

Hello everyone,

I am stuck with the following scenairo:

I have a report that shows measures visualized in Slicer and related infographics such as bar charts, tables etc.

I would like to create a measure based on the following requirements:

1. If one of the "groei.." or "instroom.." measures in the variable Firsmonth are selected in the Slicer then show the output based on the max of the field Bezetting[Month]. Other wise blank.
2. If one of the "uitstroom..." measures mention in the variable Secondmonth are selected in the slicer then show the outpute based on the max of the field Bezetting[Uitstroom Month]. Other wise blank.

I currently have the following measure but unfortunatly it doesnt work. 

Measure B = 

VAR FirstMonth = IF(

SWITCH(TRUE(),

VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei % afgelopen maand",'Kengetallen groei'[groei % mnd],

VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei % laatste 12 maand",'Kengetallen groei'[groei % 12 mnd],

VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei in aantal mdw afgelopen maand",'Kengetallen groei'[groei aantal mnd],

VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei in aantal mdw laatste 12 maand",'Kengetallen groei'[groei aantal 12 mnd],

VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw ingestroomd afgelopen maand",'Kengetallen groei'[instroom mnd],

VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw ingestroomd laatste 12 maand",'Kengetallen groei'[instroom 12 mnd]),

MAX(Bezetting[Month]),

BLANK())

VAR SecondMonth = IF(

SWITCH(TRUE(),

VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw uitgestroomd afgelopen maand",'Kengetallen groei'[uitstroom mnd],

VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw uitgestroomd laatste 12 maand",'Kengetallen groei'[uitstroom 12 mnd]),

MAX(Bezetting[Uitstroom month]),

BLANK())

return BLANK()

I cant find any solution to change my current measure. Any help/suggestions would be very welcome. 

Kind regards!

[Anonymous · Accepted solution]

Hi @rsanyoto  ,

The last return of the function you used should be followed by the value you want to display. You return blank() here, so the results must be empty

If you have a date requirement here, you can write as follows:

Measure B = 
VAR _FirstMonth = IF(
SWITCH(TRUE(),
VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei % afgelopen maand",'Kengetallen groei'[groei % mnd],
VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei % laatste 12 maand",'Kengetallen groei'[groei % 12 mnd],
VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei in aantal mdw afgelopen maand",'Kengetallen groei'[groei aantal mnd],
VALUES('Kengetallen groei'[Groei kengetallen]) = "Groei in aantal mdw laatste 12 maand",'Kengetallen groei'[groei aantal 12 mnd],
VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw ingestroomd afgelopen maand",'Kengetallen groei'[instroom mnd],
VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw ingestroomd laatste 12 maand",'Kengetallen groei'[instroom 12 mnd]),
MAX(Bezetting[Month]),
BLANK())

VAR _SecondMonth = IF(
SWITCH(TRUE(),
VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw uitgestroomd afgelopen maand",'Kengetallen groei'[uitstroom mnd],
VALUES('Kengetallen groei'[Groei kengetallen]) = "Aantal mdw uitgestroomd laatste 12 maand",'Kengetallen groei'[uitstroom 12 mnd]),
MAX(Bezetting[Uitstroom month]),
BLANK())

return 
SWITCH(TRUE(),
date = date1, _FirstMonth,
date = date2, _SecondMonth,
blank())

For the usage of VaR, you can see the following documents:

https://dax.guide/st/var/

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

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