Forum Discussion

ChiveMran's avatar
ChiveMran
New Member
1 year ago

IF statement changes inner join to cross join and blank rows

Hello All,

 

Below is a mockup of a problem I am facing for the past few days when creating a Flag based on a row level calculation.

 

Dimension Table

 

 

Fact Table

 

 

 

Relationship

 

 

Basic Visual

 

 

Step 1 of adding a basic measure

 

 

 

Step 2 where the error starts popping up

 

 

Here the number of rows changes from 6 to 30. Thus an inner join changes to a cross join. I am not sure why the blanks are appearing as I was expecting just 6 rows with 2 BAD and 4 GOOD.

 

Here I have not created a column itself because the constant 100 used in step 1 will be dynamic based on a parameter so I have tried to keep the sample file as simple as possible.  

 

Pbix file link since I am not able to upload files here.

https://we.tl/t-N6sPA5Zlmf

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    ChiveMran 

    Measure 2 = 
IF ( NOT ISBLANK ( [Measure 1] ),
    IF([Measure 1]> 250, "GOOD", "BAD")

    )
    • ChiveMran's avatar
      ChiveMran
      New Member
      1 year ago

      Thanks parry this seems to work, can you please answer these follow up questions if possible ? 

       

      1) Firstly my assumption is that the way I have setup the relationship  when using data from the two tables in the context of a worksheet an inner join should happen. So any idea why a cross join happens the moment I had added the measure ? If it is too complex then you can also point me to something and I can deep dive.

       

      2) Is there an alternative where I can add a filter to the visual as by the logic proposed I would need to edit all the visuals in my dashboard. Not a lot of rework but just asking. 

       

      3) Is the approach used by me of having a dimension and fact table correct ? I come from a background where we used to use a one big table but I created a dimension and fact table as a lot of videos on youtube had suggested this ? Though I was skeptical I just broke up my one big table into smaller chunks in SQL server, so this model is correct right ?