Forum Discussion
Resolver IFilter column only if variable is not blank
I figured it out.
Instead of the "switch values=a and values<>b, a1, values=b and values<>a, b1, else blank" code, i needed to do "if single value selected, then switch value=a, a1, value=b, b1; else blank"
And instead of "calculate column, if not isblank, filter", i needed to do "if isblank, column; else calculate column, filter"Here's the code:
Actual Sell Thru (Channel) = VAR EquivalentStoresSegment = IF(COUNTROWS(VALUES('Product'[Is Retail Or Commercial SKU]))=1, SWITCH(TRUE(), VALUES('Product'[Is Retail Or Commercial SKU]) = "Retail", "Stores Consumer", VALUES('Product'[Is Retail Or Commercial SKU]) = "Commercial" , "Stores Commercial" ), BLANK() ) RETURN IF(ISBLANK(EquivalentStoresSegment), [Actuals Sell Thru (Units)], CALCULATE([Actuals Sell Thru (Units)], REMOVEFILTERS('Product'[Is Retail Or Commercial SKU]), KEEPFILTERS('Business'[Stores Surface Segment]=EquivalentStoresSegment) ) )
Super UserFrom your OP, it sort of looks like you are trying to implement a virtual relationship between Product and Business (or Business/Product to whatever tables are involved in measure [Actuals Sell Thru (Units)])?
If I understand correctly, and you have all your relationships set up already (i.e., no need for virtual relationship), a check that could work:
VAR _valsSelected = ALLSELECTED( 'Product'[Is Retail Or Commercial SKU] )
VAR _valCheck =
ISEMPTY( EXCEPT( _valsSelected, { "Commercial" } ) )
|| ISEMPTY( EXCEPT( _valsSelected, { "Retail" } ) )
If you want to implement in a calculation, it would look something like:
Measure Hide When Multi-Selected =
VAR _valsSelected = ALLSELECTED( 'Product'[Is Retail Or Commercial SKU] )
VAR _valCheck =
ISEMPTY( EXCEPT( _valsSelected, { "Commercial" } ) )
|| ISEMPTY( EXCEPT( _valsSelected, { "Retail" } ) )
RETURN
CALCULATE( [Actuals Sell Thru (Units)], FILTER( Business, _valCheck ) )
Explanation, when _valCheck = TRUE, the filter from Business on [Actuals Sell Thru (Units)] behaves normally. When _valCheck = FALSE, then all Business rows that would normally be in the filter context now get removed.
You can similarly set this up in a measure for filter purposes:
Measure Filter Hide When Multi-Selected =
VAR _valsSelected = ALLSELECTED( 'Product'[Is Retail Or Commercial SKU] )
VAR _valCheck =
ISEMPTY( EXCEPT( _valsSelected, { "Commercial" } ) )
|| ISEMPTY( EXCEPT( _valsSelected, { "Retail" } ) )
RETURN
IF( _valCheck, 1 )
Put this in the filter well of a visual and set to 'is not blank'.
Quick gif showing the above with some test data I threw together.
Resolver IThe relationships don't already exist. I have a whole set of fact tables, most of which are filtered by Is Retail Or Commercial via a relationship to the Product table. The fact table containing Actuals, on the other hand, is filtered by Stores Surface Segment via a relationship to the Business table. There is no valid relationship between Business and Product.
Functionally, I want to filter on Retail vs Commercial, whether I use Is Retail or Commercial SKU or Stores Surface Segment to do it. I want to have a single slicer to select Retail vs Commercial, which will filter the fact tables based on Is Retail or Commercial for all fields except Actuals.
For Actuals, I want it to filter based on the Stores Surface Segment value that is equivalent to the selected Retail vs Commercial slicer value. If the Retail vs Commercial slicer has both or neither value checked, do not filter Stores Surface segment.
The fact that there is no valid relationship between Business and Product is the core of my problem, and is why I'm trying to write a calculated measure to set up an equivalency.
- lbendlin
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - MarkLaf
Provide some representative dummy data, and people can provide a more specific solution.
Note that I would consider getting the 'only apply filter when one value is selected in a silcer' behavior as a separate issue from getting a physical or virtual relationship working. You should do the latter first, and then build the former on top of it.
From what I'm understanding, you have a few options for setting up the relationship you need:
1) You can add a calculated column to Actuals, pulling over RELATED Business[Stores Surface Segment], converting to applicable Product[Is Retail or Commercial SKU] value, then relating Product[Is Retail or Commercial SKU] to Actuals[New calc column] with many-to-many, single filter direction (Product filters Actuals) relationship. Now, Product will filter Actuals.
2) Create a bridge table between Product and Business, and leverage that relationship in a filter measure on a visual with Actuals, such that a Product[Is Retail or Commercial SKU] slicer will impact the visual. See https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#factless-fact-tables.
To get really in the weeds, check out the following (translating their example to yours, I think: Business = Customer, Product = Sport, Actuals = Sales, CustomerSport is the bridge table you would want to create): https://www.sqlbi.com/articles/different-options-to-model-many-to-many-relationships-in-power-bi-and-tabular/
3) Set up a virtual relationship (preferably with TREATAS). From what I can tell, this is what you are trying to do in the measure you shared up top. See https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
Example of what this might look like:
Actual Sell Thru (Channel) = VAR _prodBizEquivs = SUMMARIZE( SUMMARIZECOLUMNS( Product[Is Retail Or Commercial SKU], "BizEquiv", SWITCH( SELECTEDVALUE( Product[Is Retail Or Commercial SKU] ), "Retail", "Stores Consumer", "Commercial" , "Stores Commercial" ) ), [BizEquiv] ) RETURN CALCULATE( [Actuals Sell Thru], TREATAS( _prodBizEquivs, Business[Stores Surface Segment] ), Business )In the above, [Acutals Sell Thru] is just a "normal" agg, e.g.
Actuals Sell Thru = SUM( Actuals[Revenue] )
