Swicth Function outcome used as slicer

[Anonymous]

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!

[Anonymous]

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.

[Anonymous]

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. 😕

[Anonymous]

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.

[Anonymous]

Hi daxer,

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

[Anonymous]

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.

[Anonymous]

Alright, thanks!

[amitchandak]

@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

[Anonymous]

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?