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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AndreiK15
Helper II
Helper II

DAX Measure won't return a proper result

Hi,

 

I'm struggle with this easy measure and I can't figure it out what's wrong. I've tried all filters combinations and still get the same result as soon as I add hours intervals. See pictures below:

 

AndreiK15_0-1633536744457.png

 

AndreiK15_3-1633536818935.png

That's the measure I use atm for Sales Qty

 

VAR vFilterContext =
FLOOR (
    LOG10 (
        SIGN(ISCROSSFILTERED ( 'Merchandise Structure') +
             ISCROSSFILTERED ( 'Articles') + 
             ISCROSSFILTERED ( 'Product Lists') +
             ISCROSSFILTERED ( 'Promo Classification' ) + 
             ISCROSSFILTERED ( 'Sales Classification' )) * 10 + 1
    ),
    1
)
VAR vResult =
SWITCH (
    vFilterContext,
        0, SUM('POS Sales'[Sales Qty]),
        1, SUM('Sales'[Sales Qty])
)
RETURN
    vResult

 

Also, I've tried different measures using all filter functions so far. What am I doing wrong?

 

Please, help!

 

Thank you!

 

 

11 REPLIES 11
PaulDBrown
Community Champion
Community Champion

can't you replicate the tables with sample data in Excel and post them?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrownwould that be ok? That's exactly what I need to achieve.

 

AndreiK15_0-1633543007165.png

 

Thanks!

 

We need actual raw data! 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown  thank you for your help and don't try to be rude, but giving you a raw data set won't fix the problem and won't provide me a solution. I will wait for a MS expert to try to solve it. The issue is not from the data. The issue is with the filters and relationships between tables.

 

If you need a raw data set, use randbetween on Excel and generate some data set. Create some categories on the left and timeframes on top and you have a data set, but won't provide a solution.

 

Thanks again!

I have access to a number of sample data sources, but unless you help me understand what you are trying to calculate, and the reason for the crossfilters for tables not shown in your depiction of the model, and the expected output, it is not possible to provide help. 
I'll leave it to a an MS expert.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown  db I'm connected to have few hundreds of tables and probably billions of row. Hours interval is on POS Hours tables and concted to POS Sales tables as a one-to-many relationship.

Sales is linked like this:

AndreiK15_0-1633552631864.png

 

and POS Sales is linked like this:

 

AndreiK15_1-1633552667631.png

 

I believe this would be much helpful than provide a data sample.

I think you're missing the point. They're trying to come up with a solution for you but to do that effectively, they're asking that you provide some sample data to work with. 

Please read: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

@KNP  sorry, but how a sample data would find a solution? Maybe I do miss a point here and I apologize, but my problem is not on creating a simple measure (or maybe it's simple). With a sample data you it will work even if you use =sum(table[qty]). Anyway, if nobody can't help me on this, I would try to figure it out by myself and again, I do apologize. Didn't try to ofend anyone. Still believe a data sample with hours intervals on top and some categories on left with a bunch of random number won't fix my problem.

The sample data doesn't find the solution. It gives us data to work with to find the solution so we don't have to create it manually. 

Maybe start with a screenshot of your complete data model and the relationships.

You've got a lot of cross filtering going on, we need to see the entire picture. 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
amitchandak
Super User
Super User

@AndreiK15 , what are you trying to achieve here ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandakI am trying to get Sales Qty per hour intervals for each category on the left and their totals. I can't share a sample as I'm connected to SASS and a sample won't make a difference. I've made a screenshot with a matrix and relationships between pos sales (from where I get sales qty) and pos hours from where I get hours intervals. Also, the main measure I use to get sales qty (which can be changed if necessary).

 

Thanks!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.