Using filter results as basis for another query

[Orstenpowers]

Dear all,

I despair of extracting some specific information out of my PBI desktop file…

Having a list of thousands of orders, I already created a column that combines the order number (9 digits) and the order position (min 2 digits).

Formula is as follows: [9digit_Sales_order_no] & "-" & [SALES ORDER POSITION]; both coming from DetailedReporting

Positions 10, 20, 30 are main positions.

Positions 11, 12, 21, 22, 23 etc. are sub positions.

In the end, I will need some information from the main positions, but before I can do so, I need to check some information being available from the sub positions only.

Just a few examples:

010123456-10

010123456-11

010987654-10

010987654-11

010987654-20

010987654-21

010987654-22

My problem is that only the main order positions are of interest and these are always ending with 0.

Looking at the orders above, I would like to see the information for the related tenner.

010123456-10

010123456-10

010987654-10

010987654-10

010987654-20

010987654-20

010987654-20

I do not know how to solve it, but from my point of view the steps would be.

1. Separate the order no from the order pos
2. Consider the order pos as digit and then rounddown (in Excel I just would say ROUNDDOWN(order pos;-1)
3. Combine the order no and the rounded order pos
4. For this new combination, return the value of DetailedReporting[Sub-family]

Was this explained reasonably?
Is it possible? Where and how?

Hopefully someone from you guys can help me!?

[Orstenpowers]

@v-xulin-mstf 

Good Morning!
does my above explanation help to understand the second issue?

Please let me know.

Best regards!

[v-xulin-mstf]

Hi @Orstenpowers

Sorry for delay reply.

You can try measure as:

Measure = 
CALCULATE(
    CALCULATE(
        SELECTEDVALUE('Table'[SUB-FAMILY]),RIGHT('Table'[SALES ORDER POSITION],1)="0"),
    FILTER(
        ALL('Table'),
        'Table'[MAIN_ORDER_POS]=MAX('Table'[MAIN_ORDER_POS])
    )
   )

Here is the output:

The pbix is attached.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Orstenpowers]

Hi @v-xulin-mstf ,

You do not need to apologize. 🙂

I tried your proposed measure.

On the one hand, it seems to work, but applying this measure also reduces the illustrated data significantly and it is eye-catching that only SALES ORDER POSITIONS with 4 digits are illustrated!?
Do you have any idea how to overcome this? Your PBI demo file seems to work perfectly...

[Orstenpowers]

Hi @v-xulin-mstf ,

Do you have any idea how to overcome this?

Best regards,

Orstenpowers

[v-xulin-mstf]

Hi @Orstenpowers

Sorry, i'm not clear about your issue.

Could you provide more details or share your pbix?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

Hi @Orstenpowers,

I'm not clear about your issue.

Could you provide screenshot or error message?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

Hi @Orstenpowers,

It is possible to use rounding, but it may be complicated by the conjunction '-'.

Maybe you can use RIGHT function to filter out the number with the rightmost digit "0".

You can try measure as:

Measure 2 = 
IF(
    RIGHT(MAX('Table 1'[ID]),1)="0",
    1,
    0
)

Here is the output:

Best Regards,
Link

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

[Orstenpowers]

I reduced my data amount to check your proposal. Unfortunately the result always is 0.

[v-xulin-mstf]

Hi @Orstenpowers

That's strange, could you provide your sample data or pbix file after removing sensitive information?

Best Regards,
Link

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

[Orstenpowers]

Dear v-xulin-mstf,

Your solution might work, but I get an error message that the visualization cannot be illustrated. The details state that missing main memory might be the reason...my RAM is 16GB, I cannot believe.

[v-xulin-mstf]

Hi @Orstenpowers

Please clear cache and check:

Best Regards,
Link

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

[Orstenpowers]

Dear Link,

I prepared a sample file. 😊

I have two columns, both with data type: Text (necessary)

1. 9digit_Sales_order_no
2. SALES ORDER POSITION

The SALES ORDER POSITION can be 10, 11, 12, 20, 21, 22, 23, 30 etc.

Now I need a customized column (name should be “MAIN_ORDER_POS”) that rounds the SALES ORDER POSITION. Column E in the sample file.

10 to 10

11 to 10

20 to 20

21 to 20

22 to 20

23 to 20

etc.

Then the 9digit_Sales_order_no and the new column should be combined. I think the following formula should work!?

MAIN_ORDER_NO_AND_POS = [9digit_Sales_order_no] & "-" & [MAIN_ORDER_POS] Column F in the sample file.

And now it really gets difficult.

In column G of the sample file, the values of columns F and C should be compared, returning the value of column D (SUB-FAMILY)

Do you have any idea how to realize in PBI? I do not have…

[Orstenpowers]

[v-xulin-mstf]

Hi @Orstenpowers,

Here is the expected output of the original post:

You can create columns as:

The data type of [column] must be whole number.

Column =

DIVIDE('Table'[SALES ORDER POSITION],10)

MAIN_ORDER_POS =

CONCATENATE('Table'[Column],"0")

MAIN_ORDER_NO_AND_POS =

CONCATENATE(CONCATENATE('Table'[9digit_Sales_order_no],"-"),'Table'[MAIN_ORDER_POS])

The pbix is attached.

I'm not sure about the second issue, could you provide more detials?

Best Regards,
Link

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

[Orstenpowers]

[Orstenpowers]

Hi Link,

So far, your formula work perfectly. Thank you so much!

I updated the screenshot to better explain my request.

As an example, let’s use MAIN_ORDER_NO_AND_POS 441070744-30 in column F.

Column G now should state the result of column D “Lavamat 1000”, when information of column F = column C.

Better explained?