filter tables with slicer & filter + Power bi queries issue

[MP_123]

hi,

i have 'fact' table with all my measures and dimensions. my data is divided to combinations.

the second table is combinations table . two tables are related with combination ID.

what combinations mean? you need to select only values from specific column to get measures.

for example

 one combination is date + product name,

second is date + product name + product type

every combination have ID + string attached to it.

i created calculated measure that concatenates values to a string. for example date = 'a' product name = 'b', product type = 'c' (the measure concatenates only if each of the slicers has been  filtered)

means that if i want to use the first  combination i need to select only date and card type.

after i have my measure = 'ab' i filter the combination table with filter(combinations,combinationString=MyMeasure)

i created a report with this structure and its working fine.

when trying to create another report with this structure, i encountered strange issues

after i saw that i don't get the result i'm expecting to, i checked on the DB the queries power BI sent.

i saw that for different values on slicers, i get different queries. sometimes i get "select . . . where combination ID=1", sometimes i get "select.. where combination ID in (1,2)"

i select the same slicers but just changing it's values. the issue that when the query is with the "in", i get duplicates.

have somone encountered this problem with power bi? or have a better idea to implement my data source?

thanks a lot! sorry for the long story

[CahabaData]

I must admit I do not really follow/undestand what you are saying.  But some of the symptoms you describe are consistent with either incorrect or just unexpected join lines between tables.  You should inspect the relationship view.  Join lines can create by themselves I have found.

Otherwise I suggest providing a brief generic sample of your source data table - and an example of your goal results data table. 

[MP_123]

thank you! another explanation - 

this is the first table : 

you can see the combination ID column, and 'Combination1' column.

this is the fact table:

there is a 'one to many' relationship - in the fact table there is only one row that related to, on the combinations table.

combinations table has distinct values for both ID, and the Combination 1 column.

in my report, i have calculated measure which concatenate chars into one string:

lets look on the report: 

if you look at the slicers on the left side, you see only 4 out of 7 slicers has been filtered.

each slicer that sliced, gets a value:

for example: MonthString = if(ISFILTERED('Fact'[Month]),"d","") means that the measure = d in this case.

that what i do to all of the slicers, in this case because i unselected some of the slicers, the string value will be "".

then i concatenate all values to one string - you can see it on center right, CombinationString Measures.

it's value is cdlo : c - Card Group, d for date, l for 'is self host' o for market.

now i created another calulcted measure:

Notifications Shown = CALCULATE(sum('Fact'[UniqueUserCount_Rendered_Daily]),FILTER('Combinations',Combinations[combination1]=[CombinationString]))

what the filter does? take the string i concatenated (cdlo) and filter the combinations table with this value - means that i need to get only 1 value! with this one value (because of the relationship) the fact table is filtered also.

you can see on the report print screen, on the table visual, that there are 3 rows which has been calculated, why is that? combinationString has only one value = cdlo, why do i get results for cdlos, and cdlost also?

hope that it more clear right now! thanks!

@CahabaData