Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aggiebrown
Helper III
Helper III

DAX formula to only calculate values based on TRUE/FALSE or 0/1 Boolean calc column in the fact tabl

What DAX formula can I use to only calculate values/numbers based on a calculated column in the fact table being "0" or "1" (true/false format)

 

I have a fact table which shows unique account numbers, transaction ids etc. 

 

It was a long merge process in power query to get to the result that will allow me to build one fact table to use in front end,.

 

In order to compare the facts some of the transaction IDs are duplicate (have 4 rows etc)

How can I then build a measure that will count the value of this transaction only once in the measure.

 

Example of fact table shown below:

 

Have only 4 unique transactions in total - the value sold should only be 699, but with every DAX measure I try it is triple counting 99 and therefore giving me 897.

 

Moreover my second argument is that I only want to sum Unique transactions which Sold After Interaction so I am looking for help in creating a DAX Measure (maybe using var) that will filter Unique Trans IDs and Sold After interaction > 0 and then Sum the Column "Value of Sold"

 

Trans IDValue of SoldSold after Interaction
10199.001
10199.001
10199.001
102150.001
103200.000
104250.001

 

 

Any help / ideas would be appreciated.

 

1 ACCEPTED SOLUTION

Hi @aggiebrown ,

 

Try the following formula:

 

Measure = 
SUMX(
    DISTINCT(
        FILTER(
            'Table',
            'Table'[Sold after Interaction] > 0
        )
    ),
    'Table'[Value of Sold]
)

vkkfmsft_0-1629350557696.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

View solution in original post

6 REPLIES 6
v-kkf-msft
Community Support
Community Support

Hi @aggiebrown ,

 

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

 

Best Regards,
Winniz

amitchandak
Super User
Super User

@aggiebrown , Try a measure like

Sumx(summarize(filter(Table, Table[Sold after Interaction] >0), [Trans ID], [Value of Sold]),[Value of Sold])

 

 

@amitchandak many thanks for your suggestion. I have tried it however PBI does not like the "> 0 " bit and underlines it with red, so can't proceed. When I get rid of the "> 0 " condition the measure just comes back as 0.00

Hi @aggiebrown 

Try this measure:

Sum of transactions = 
SUMX (
    GROUPBY (
        FILTER ( 'Table', 'Table'[Sold after Interaction] > 0 ),
        'Table'[Trans ID],
        'Table'[Value of Sold],
        'Table'[Sold after Interaction]
    ),
    'Table'[Value of Sold]
)

The output will be as below:

VahidDM_0-1629205715090.png

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos VahidDM_1-1629205767922.png !!

 

Hi, many thanks for your reply. I have tried that and it does not seem to work. 

It's worth saying I have a relationship created in the model that links back to the specific agent but I don't see how that would cause an issue.

Hi @aggiebrown ,

 

Try the following formula:

 

Measure = 
SUMX(
    DISTINCT(
        FILTER(
            'Table',
            'Table'[Sold after Interaction] > 0
        )
    ),
    'Table'[Value of Sold]
)

vkkfmsft_0-1629350557696.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.