The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello experts,
I'm working with a live model and I can't change the content of the model in my report or create calculated columns. The data model is maintained by another department.
So I'm looking for how I can do things with measures that I would normally use a calculated column for.
I want to categorize orders and filter whether a gateway was skipped because the field doesn't have a timestamp.
So I want to have a measure with a text or a number when a gateway was skipped. The timestamps themselves are not relevant.
Example:
Column 'Start - timestamp
Column 'gateway 1 - timestamp
Column 'gateway 2 - empty
Column 'end - timestamp
The measure should now output something like "Gateway 2 skipped". I want to evaluate it row by row. I think counting how often the gateway was skipped isn't enough because I want to filter and display the relevant orders later.
My attempts like "if(isblank(table[gateway 2]),..." don't work. I can't access the contents of the "gateway 1" column in the DAX formulas this way.
Can you please point me in the right direction how this can be done. Which DAX formulas or which concept can I use to solve this?
Thanks in advance.
Frank
Solved! Go to Solution.
You can Adjust the measure to use SELECTEDVALUE instead of MAX to ensure it evaluates correctly for each row.
But measures has some limitation as compared to Calculated column as mentioned by @Anonymous
Proud to be a Super User! |
|
Hi @C-F-G ,
Your solution is great, @bhanu_gautam . Here I have an idea in mind, and I would like to share it for reference.
The differences between calculated columns and measure are as follows:
Calculated Column
1.Row by row: Calculated column is calculated row by row when loading or refreshing data. The value of each row is determined independently of the other rows.
2.Static values: after calculation, these values are stored in the semantic model and will not be changed unless the data is refreshed.
3.No aggregation required: each row is calculated independently.
Measure
1.Dynamically calculated: Measure is dynamically calculated based on the context of the visual or report's filter.
2.Context-dependent: Measure depends on the current context and can be changed dynamically.
3.Aggregation required: To get the current value , you usually need to aggregate the data. Using aggregation functions such as MAX can explicitly tell the DAX engine which table and column you are referencing.
If you have any other questions please feel free to contact me.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Thank you for claifying the different concepts of calculated columns and measures.
I will try to figure out how good measures can do the job in my scenario where I can't modify the data model itself.
@C-F-G , You can try using below DAX
SkippedGateways =
VAR Gateway1Skipped = IF(ISBLANK(MAX('YourTable'[gateway 1 - timestamp])), "Gateway 1 skipped", "")
VAR Gateway2Skipped = IF(ISBLANK(MAX('YourTable'[gateway 2 - timestamp])), "Gateway 2 skipped", "")
VAR Gateway3Skipped = IF(ISBLANK(MAX('YourTable'[gateway 3 - timestamp])), "Gateway 3 skipped", "")
RETURN
TRIM(CONCATENATEX(
{Gateway1Skipped, Gateway2Skipped, Gateway3Skipped},
[Value],
", ",
[Value],
[Value]
))
These variables check if the respective gateway timestamp is blank. If it is, they store a message indicating that the gateway was skipped.
Proud to be a Super User! |
|
Thank you very much, I understood most of the concept and got it working partially.
The measure is evaluated correctly in a separate card visual when a single row is selected from the list of orders in a table visual.
But can I use this measure in the table visual itself that displays several data rows?
Gateway 1 | Gateway 2 | Result of Measure | |
order 1 | timestamp | Gateway 2 skipped | |
order 2 | timestamp | Gateway 1 skipped | |
order 3 | ... | ... | ... |
I would like to see the result of this measure evaluated for each row without having to select a row.
When I add the measure to the table visual it does not update and is running without result.
Thank you.
You can Adjust the measure to use SELECTEDVALUE instead of MAX to ensure it evaluates correctly for each row.
But measures has some limitation as compared to Calculated column as mentioned by @Anonymous
Proud to be a Super User! |
|
Thank you.
Using SELECTEDVALUE seems useful. I will try to figure out in the next days.
At the moment I'm afraid I need to extend the data model.
User | Count |
---|---|
11 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
22 | |
14 | |
14 | |
9 | |
7 |