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 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 !
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

4 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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