Referencing column values in SWITCH and IF not working but able to be done on Youtube

[USERANON]

So I am trying to make a data point that will adjust the formula output depending on the input. 

- If I have a Corrective Action Approval Date (Lets call it CAAD for short) that is a blank cell meaning it hasnt been corrected yet I want to display a DATEDIFF(Issue Date,Today(),Days)
- If CAAD = Issue date I want to display the number 1 

- If CAAD <> Blank()  (Meaning any non blank value that isnt the same as Issue date) display DATEDIFF(Issue Date,CAAD,Days)

I watched a Youtube video by a developer of Power BI he was able to use the switch funtcion and reference COLUMN values. (Pictured below)

With this method in mind my formula should be 

Measure = 

Switch(
        True(),

        CAAD = BLANK(), DATEDIFF(Issue Date,Today(),Days),

        CAAD = Issue Date, 1,

        CAAD <> BLANK(), DATEDIFF(Issue Date,CAAD,Days)

)

*This should be the right order to make sure that nonblank values that are equal to issue date are filtered as equal to issue date and not just grouped up with the other nonblank values*

However it wont let me even reference the column values only measures so the formula fails. I tried with IF and that was the same. I even tried creating a new measure using ISBLANK to try and cheat the system but DAX did not let me reference that either. I do not think I want to use the ALL funciton as I will be turning this into monthly and yearly totals for this measure and I dont want it to ignore my filters later down the road. 

A little extra context. This dashboard utilizes 5 data sources in case that alters things.

Any help would be greatly appreciated. I have been completely stumped by something that seems so simple. 

[USERANON]

Hi @Anonymous ,

First I'd like to thank you for taking the time to help look into this problem. 

I tried to use the formula as a calculated column after reading this and it was still unable to identify the columns. When I started to type them, the only suggestions I was given were functions and previously created measures. Im not sure why It won't identify the columns. I read the articles you linked and seems like your solution to use a calculated column should work but it is giving me this. 

Is there another approach I could use like create a bunch of measures? Im sure it wont help performance but this is a dashboard used once a month so I can suffer through the slowness if thats the only way I can get the dashboard to produce the numbers I need. Just as added information, all of the datasources are in DirectQuery mode

[Anonymous · Accepted solution]

Hi @USERANON ,

Did you create that calculated column in the table 'QCBD'? If no, please create a calculated column as below in the table 'QCBD' to get it:

CAPA EXT =
SWITCH (
    TRUE (),
    'QCBD'[CorrectiveAction.Corrective Action Approval Date] = BLANK (), DATEDIFF ( 'QCBD'[Issue Date], TODAY (), DAY ),
    'QCBD'[CorrectiveAction.Corrective Action Approval Date] = 'QCBD'[Issue Date], 1,
    NOT ( ISBLANK ( 'QCBD'[CorrectiveAction.Corrective Action Approval Date] ) ), DATEDIFF ( 'QCBD'[Issue Date], 'QCBD'[CAAD], DAY )
)

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

Best Regards

[USERANON]

I was trying to do it in my measures table and was unsuccesful. It definitely makes sense that you would need to make a calculated column in the table that it is pulling data from. Your remedy worked. Thank you again for helping me out I really appreciate it. 

[Anonymous]

Hi @USERANON ,

It seems that what you are creating a measure instead of a calculated column. You can create a calculated column and apply the same formula in it. Later check if it can return the expected result. 

Solved: Measure not recognizing columns - Microsoft Fabric Community

If you reference a column in a measure, you must wrap the column in an aggregation function like MIN, MAX, SUM, AVERAGE, etc.

---

Measure vs calculated column:

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

Best Regards