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

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_
Icon for Memorable Member rankMemorable 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. 🙂