Get same row count from FACT in every row

[legored44]

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 just the FACT but which contains less columns.

This should return me the same number of rows which have data in the FACT, as I filter them from the same DIM1[Project] that is in the formula. So DIM1[Project] is part of the right filter panel.

The behavior is this: I select one project, the table visual in which this formula is put gets populated with the row for the selected project, the count is 1. I select another project, the count is 2, but 2 should get displayed for both rows. I select a 3rd project which doesn't have data in the FACT, the displayed count should remain 2.

What happens now if my formula is that it just counts the number of selected rows. Because if I select 3 projects, 2 have data and one doesn't, I still get 3 instead of 2. (see pic)

Thank you !

[Anonymous · Accepted solution]

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_]

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]

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_]

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. 🙂