DAX Measure won't return a proper result

[AndreiK15]

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:

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!

[PaulDBrown]

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

[AndreiK15]

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

Thanks!

[PaulDBrown]

We need actual raw data! 

[AndreiK15]

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

[PaulDBrown]

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.

[AndreiK15]

@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:

and POS Sales is linked like this:

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

[KNP]

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

[AndreiK15]

@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.

[KNP]

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. 

[amitchandak]

@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.

[AndreiK15]

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