Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Looking for some assistance to figure out why I'm not getting what I'm expecting.
I'm building a calculated column using values selected within slicers. It works great if only one value is selected and is throwing me a curve when no values are selected.
My code is something like this :
Criteria = IF ( Table1[TextColumn] IN { IF ( NOT ( ISFILTERED ( Table2[TextColumn] ) ), [TextColumnSelectedValue], CONCATENATEX ( ALLSELECTED ( Table2[TextColumn] ), Table2[TextColumn] ) ) } ,"Meets", "Does Not Meet")
Solved! Go to Solution.
Hi @jcs93 ,
To create a measure as below.
Criteria1 = var th = VALUES(TextHereTable[TextHere]) var mt = VALUES(MoreTextTable[MoreText]) RETURN SWITCH(TRUE(), MAX(PowerBITest[TextHere]) in th && MAX(PowerBITest[MoreText]) in mt , "Meets Criteria", "Does Not Meet Criteria")
Regards,
Frank
First off, calculated columns are NOT recalculated when a slicer value changes. They are ONLY recalculated when the table is refreshed.
Do you mean you're creating a MEASURE that looks at slicer values?
I'd disagree with your statement. My column is recalculating when a slicer value is changed. I have the product working on the desktop and in the web environment.
When I say 'working,' I mean it's working when a value is selected. when no value is selected it's acting weird.
@jcs93 , do you mean you've created a column in a table VISUAL?. If so, you've probably created a measure for this calculation. Measures are re-evaluated whenever the filter context changes (ie, a slicer is changed, cross filtering from clicking on another visual, etc.).
A calculated column in a table in your data model is different than a table visual that's created in a report tab.
Screenshots would help the community solve your problem. Can you provide some?
This is an excerpt from SQLBI's article explaining the difference between Calculated Columns and Measures:
Choosing between calculated columns and measures Even if they look similar, there is a big difference between calculated columns and measures.
The value of a calculated column is computed during data refresh and uses the current row
as a context; it does not depend on user interaction in the report.
A measure operates on aggregations of data defined by the current context,
which depends on the filter applied in the report –
such as slicer, rows, and columns selection in a pivot table, or axes and
filters applied to a chart.
Ok, maybe it's not a calculated column...
On the Tables tab, I clicked on "New Column" and typed the DAX Expression. Is that not a 'Calculated Column'?
Either way, maybe we can get away from the semantics and arguments and possibly come to a solution?
Hi @jcs93 ,
As @Anonymous said, calculated columns are NOT recalculated when a slicer value changes. So you can create measures and put the measures to visuals to work on that.
Regards,
Frank
@Anonymous
You both seem to be determined to convince me that what I'm doing doesn't work, so I've created an example of what I'm doing. Please download the PBIX at Criteria PBIX
Click on values in the slicers and watch the Criteria columns change with your own eyes. Upload it to your Power BI App workspace and watch it change there.
I too was convinced by the research and reading that I had done weeks ago that this was impossible. Then I tried it out of curiosity. Much to my surprise, the impossible was possible.
When you open my PBIX, you'll see the issue that I'm trying to get past. When no slicer selections are made, all the rows in the table should "Meet Criteria," however, they don't. This is what I'm asking for help to resolve, but you both seem stuck on the fact that the impossible is still impossible.
I've only been working with Power BI for a few months and one thing I've noticed is that it is an ever-evolving product where things that were once impossible, become possible because of user requests. I commend Microsoft for that.
Hi @jcs93 ,
To create a measure as below.
Criteria1 = var th = VALUES(TextHereTable[TextHere]) var mt = VALUES(MoreTextTable[MoreText]) RETURN SWITCH(TRUE(), MAX(PowerBITest[TextHere]) in th && MAX(PowerBITest[MoreText]) in mt , "Meets Criteria", "Does Not Meet Criteria")
Regards,
Frank
Thank you. Using your code as a measure corrects my issue, but leaves one behind.
I didn't show in my demo file, but I need the ability to display the Criteria in a stacked bar chart. Sadly, I cannot insert a measure into the legend field in a stacked bar chart (or any other field).
Please advise.
Thank you,
Jim
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |