topic Re: Swicth Function outcome used as slicer in DAX Commands and Tips

Swicth Function outcome used as slicer

Anonymous — Tue, 28 Jul 2020 15:28:45 GMT

Hi guys,

I made a switch function that gives me the status of our machines ->

Threshold Last Audit = SWITCH(TRUE(),[DateDiff Last Audit]<=14,"Green", [DateDiff Last Audit]<=39,"Amber",[DateDiff Last Audit]>=40,"Red")

This works fine when i drag the serial number of the machines into the visual. However, i would like to make a slicer in which i would display "Green", "Amber" and "Red" as options to be picked, and I am stuck there. As we use direct query in our company, i cannot create a seperate table, neither add calculated columns.

Does anybody know how i can solve my issue using calculated measures only?

Thanks!

Re: Swicth Function outcome used as slicer

amitchandak — Tue, 28 Jul 2020 15:43:32 GMT

Anonymous , I think in direct query you add table using enter data. It changes the mode to mix.

rest of the approach will be like https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

Re: Swicth Function outcome used as slicer

Anonymous — Tue, 28 Jul 2020 17:52:25 GMT

Hi amitchandak,

Thanks for your feedback! Unfortunatelly, i cannot "enter data", this option is greyed out to me.

For the suggested approach, i would need to create a diconnected table, which i cannot do due to the direct query.

Any other ideas to solve my issue?

Re: Swicth Function outcome used as slicer

Anonymous — Wed, 29 Jul 2020 13:18:03 GMT

Hi there.

As far as I know, you can use a SQL query as a source for your tables. Here's the query that you should use:

SELECT t.Status, t.StatusOrder FROM ( VALUES ('Red', 1), ('Amber', 2), ('Green', 3) ) AS t([Status], [StatusOrder])

Since it does not require any tables from your database, it should work OK. [StatusOrder] is there to be able to sort the statuses correctly, not alphabetically. When you press Get Data, you'll select the source to be your SQL Server, then you'll expand the section Advanced and input your query in the box. Easy. I've checked it and it works.

Re: Swicth Function outcome used as slicer

Anonymous — Wed, 29 Jul 2020 13:46:39 GMT

Hi daxer,

Thanks for your feedback. Unfortunatelly, i dont have access to SQL.

My challenge is to build the slicer based only on calculated measures. 😕

Re: Swicth Function outcome used as slicer

Anonymous — Wed, 29 Jul 2020 14:10:08 GMT

If you don't have access to import data into your model... then how do you get the data in the first place? Your challenge is not a challenge. You simply CANNOT create a slicer from measures. Simple as that. You have to create a table, be it through import or a calculated one. Nothing else will do. Sorry.

Re: Swicth Function outcome used as slicer

Anonymous — Wed, 29 Jul 2020 14:13:34 GMT

Hi daxer,

I have a pre-set query from HANA, i just pick a table from there. does that make it clear for you?

Re: Swicth Function outcome used as slicer

Anonymous — Wed, 29 Jul 2020 15:17:06 GMT

OK. You should have stated this at the beginning... If you can't use SQL or some other query language, then you cannot create a slicer. You have to ask the admins of your system to create the table in the source for you.

Re: Swicth Function outcome used as slicer

Anonymous — Wed, 29 Jul 2020 15:28:00 GMT

Alright, thanks!