Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Swicth Function outcome used as slicer

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!
Calculated Measures Only
Slicer
switch

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        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.