Forum Discussion

legored44's avatar
legored44
New Member
2 years ago
Solved

Get same row count from FACT in every row

Hello, I have the following formula, put in a table visual in Power BI:   CALCULATE(SUMX(_summarizedfact, DISTINCTCOUNTNOBLANK(DIM1[Project])), ALLSELECTED(DIM1))   Where _summarizedfact is jus...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi legored44 ï¼Œ
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:
    Fact


    DIM1


    DATA MODEL

    Create measure

    ProjectCountTable = 
CALCULATE(
    SUMX(
        FILTER(
            'Fact',
            NOT ISBLANK('Fact'[Project])
        ),
        1
    ),
    ALLSELECTED(DIM1)
)

    Final output

     

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Wilson_'s avatar
Wilson_
Memorable Member
2 years ago

Hi legored44,

 

The issue is you're iterating over the fact table, but your expression is using your dimension table. The fact table is not filtering your dimension table, so your current measure is the equivalent of writing COUNTROWS ( DIM1 ).

 

If you're looking for only the rows that have data in your fact table, I believe what you want is:

CALCULATE (
    COUNTROWS ( VALUES ( _summarizedfact[Project] ) ),
    ALLSELECTED ( DIM1 )
)

 

This way you keep the filter from the outside slicer, ignore the Project filter within the visual and perform your calculation on the fact table. Let me know if that's not what you were looking for.


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

legored44's avatar
legored44
New Member
2 years ago

Hi Wilson_  , thank you for taking a look.

 

The issue with your solutions is that I can't write 

COUNTROWS ( VALUES ( _summarizedfact[Project] ) )

 

because I can't access the columns of my _summarizedfact, probably I should have mentioned that is a table obtained with the SUMMARIZE function, in DAX.

 

Also, second problem and sorry for not mentioning in my OP, but in the FACT I have ID_Project, and not Project, so the relationship between the FACT and the DIM is done through the ID_Project. For example in the DIM I can have the same Project with multiple ID_Project, depending on the characteristics.

 

I tried to implement your solution with a workaround, by first using ADDCOLUMNS to add the Project column to the fact, and then counting over that, like this:

 

VAR _summarizedfact2 = ADDCOLUMNS(_summarizedfact, "project_dim", DIM[Project])

RETURN CALCULATE(COUNTROWS(SELECTCOLUMNS(_summarizedfact2 , "project", [project_dim])), ALLSELECTED(DIM))

 

But with this I just get a 1 for every row, so if I have let's say 3 projects selected in the filter, 2 with data and 1 with no data in the FACT, I get 1 for each line of the 2 projects that have data. (instead of 2 in each line). So it still doesn't solve my problem to have the total count in every row.

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂