DAX Help

[DebbieE]

I cannot work this DAX out at all.

I have a Fact table to count items.

A Dim of Items. Item ID is -1 where there isnt an item

and a Dim of Project requirements

I want to know all of the requirements without items

I have underlying DAX to get the Distinct Count of Project requirements (which someone helped me with a while ago. ensuring the id in Dim is linked to the fact table

CALCULATE(

    SUMX(

        VALUES('Dim Proj Req'[Req ID]),

        1

        ),

        'Dim Proj Req'[Req ID]<> "-1", // This is so we dont count the default no item row

        'Fact table')

And I have tried to create  DAX using the above like this

Total Reqs No Items =

CALCULATE( total Req,'Fact'[Item Key]= "-1")

It is -1 where there isnt a document

But its still giving me a 1 against Item IDs and I can't figure out why? 

E.g.

Req ID Item Key Item ID Total No Items

1          1            I1                   1

1           2           I2                   1

2          -1           -1                  1

So only the last one should have No Items = 1

I just cannot get this working. Can anyone give me an idea where Im going wrong?

[Anonymous]

Hi @DebbieE ,

Thanks for the reply from Greg_Deckler .

Your requirement is: filter the number of Req IDs when Item Key = "-1". Am I understanding it correctly?

You can modify the measure syntax to:

Measure = 
CALCULATE(
    DISTINCTCOUNT('Dim Proj Req'[Req ID]),
    FILTER(
        'Fact',
        'Fact'[Item Key] = "-1"
    )
)

Remember to create the correct relationship between your Dim table and Fact table.

I would be grateful if you could provide me with the pbix file.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[DebbieE]

Its ok. I can't get you the pbix file. I checked the relationships in the model numerous times and its all correct.  And I have decided not to go forward with this DAX and just do something simpler instead. 

If I can't get my head round it. I can't leave it for someone else to look after

[DebbieE]

 I have given up on this DAX

In the Dim i added a New Column On the -1 Default one I have No item and everything else as Item.

Then i can use the First DAX for num of Project Requirements on a bar chart with the Item Status descriptor Creating two bars.

not what i wanted but the DAX has beaten me yet again. 

[Greg_Deckler]

@DebbieE Here's a possible solution. This one is a replacement for your original measure:

Measure = 
VAR __Result =
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER( 'Dim Proj Req', [Item ID] <> "-1" ),
                "__RecID", [Req ID]
            )
        )
    )
RETURN
    __Result

And this one is the second measure. PBIX is attached below signature.

Measure 2 = IF( [Measure] = BLANK(), 1, BLANK() )

[Greg_Deckler]

@DebbieE Well, for one, the original formula doesn't seem quite correct as none of the Rec ID's in your table are -1 and thus nothing will be filtered. Let me see if I can create a good solution for this.

[DebbieE]

I was just thinking 

 FILTER( 'Dim Proj Req', [Item ID] <> "-1" ),

Items ID is in Items Dim and not Dim Proj Req so the above doesnt quite get there 

[Greg_Deckler]

@DebbieE If you can post a sample PBIX to OneDrive or Box or post all sample data for the issue that would help otherwise it's just guesswork. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[DebbieE]

See above. i have given up with this one and created an extra column in the dimension