Multiple rows while adding to grid data from dimension

[Anonymous]

Hi Everyone,

Thank you for your time, Before getting into the problem directly I'll tell you the background of this.

I'm showing on a grid visual unique IDs from the fact table and a measure that checks if there is value in a specific column in the fact table for the selected id.

Measure:

SLA Status = 
   ISBLANK(SELECTEDVALUE('Alerts'[handledAt]))

the results before adding data from dimension:

everything works fine until I add data from dimension.
then instead of showing the related data from the dimension for each ID, it generates a cartesian join with the data from the dimension.  
the results after adding data from the dimension:

I know there are alternative ways to calculate "SLA status", but my question is why it is even happening?

Thanks for your help

[tamerj1]

Hi @Anonymous 

When you add a column to a table visual it becomes part of the filter context and apparently this is what causes trouble in obtaining the desired results. In order to avoid that you need to add the Alert Status as a measure. You my use SELECTEDVALUE wraped with CLACULATE in in order to CROSSFILTER the relationship as "Both"

[Anonymous]

Why the filter contest is causing this problem?
Why does creating a BI-directional relationship should solve the problem?

[tamerj1 · Accepted solution]

@Anonymous 

You can see that once you add the column each id becomes repeated 6 times which is the number of unique values of the dim table column. The reson is that dim filters fact but not the opposite. Therfore the engine creates a cross join table which becomes the new filter context of the visual. 
what I meant is that if you want to show the corresponding Alert Status in the visual without creating problems thenyou have two options:

the first one as I mentioned you can create a new measure 

Alert Status = CALCULATE ( SELECTEDVALUE ( DimTable[AlertStats] ), CROSSFILTER ( DimTable[column], FacTable[column], Both)

then use the measure in your visual. 

2nd option is to create a calculated column in the FacTable using RELATED (DimTable[AlertStats]))

then use column in the visual.  

[Anonymous]

Thank you for your clear explanation. 
Why there are no columns except "id" (alert id from fact) and "status" (from dim) it didn't happen ?
only when add the measure.

[tamerj1]

Hi Adiel

Would you please clarify further on your question?

[littlemojopuppy]

Hi @Anonymous I'm guessing that if you dropped a date/time field into the visual it's producing the history of each alert through the statuses.

What you're intending to show is the most recent status for each alert?

[Anonymous]

Hi @littlemojopuppy ,
It's not events\log table, so this isn't the situation.
each alert appears once in the table.