Replacement for FIRSTNONBLANK in calculated column in DirectQuery mode

[Anonymous]

Hello,

I am trying to recreate dynamic report from this video: https://www.youtube.com/watch?v=aQKDHy4_pgs
but I have to work in DirectQuery mode. I was able to build all but one dynamic column. I can't use FIRSTNONBLANK function.
= SWITCH(

FIRSTNONBLANK ('Dynamic Slicer'[Metric Choice], 1 ),

"Product", RELATED ('Internet Sales' [Product Name]),

"Promotion", RELATED ('Internet Sales' [Promotion Name]),

"Promotion Category",  RELATED ('Internet Sales' [Promotion Category]),

"Sales Region", RELATED ('Internet Sales' [Sales Region])

)

Any ideas how I can modify above DAX to create calculated column in DirectQuery?

Thank you!

[Anonymous]

Hi @Anonymous ,

Has your problem been solved?

According to @lbendlin ‘s suggestion you can check the following example.

Measure with FIRSTNONBLANK = SWITCH(

FIRSTNONBLANK ('Dynamic Slicer'[Metric Choice], 1 ),

"Product", "expression1",

"Promotion", "expression2",

"Promotion Category",  "expression3",

"Sales Region", "expression4"

)

Now we can replace FIRSTNONBLANK with MAX.

Measure with MAX = SWITCH(

MAX ('Dynamic Slicer'[Metric Choice]),

"Product", "expression1",

"Promotion", "expression2",

"Promotion Category",  "expression3",

"Sales Region", "expression4"

)

Both of these measures return the same result. And the MAX function is applicable in DQ mode.

Best Regards,

Stephen Tao

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

[lbendlin]

Have you tried using MINX or CALCULATE(MIN()) or similar techniques? What business story are you trying to tell?

[Anonymous]

@lbendlin  It wont work with DirectQuery mode I have to use