Firstnonblank do not find right next not blank in other table

[MaleneL]

Hi all

I am struggling with a firstnonblank issues that I cannot see how to fix.

I have one table with multiple customers and the each have at set of accounts:

indeks	Customer	Account	Value	Division
1	1	152	4.568.786
2	1	153	555.522	Cars
3	1	154	154.654	Cars
4	1	155	5.555	Dogs
5	1	156	2.222	Cars
6	2	157	4.568.796	Dogs
7	2	158	555.532	Dogs
8	2	159	154.664	Dogs
9	2	160	5.565	Dogs
10	2	161	2.232	Dogs
11	3	162	4.568.806	Cats
12	3	163	555.542	Cats
13	3	164	154.674	Cats
14	3	165	5.575	Cats
15	3	166	2.242	Cats

Every Month I get a new list (which of course have a date as well). With this list I want to find the Division to another table with only the customer number in it. So easy!  make a FIRSTNONBLANK :O).
But the trouble is, that for some of the accounts there is no Division, so I want to make a Dax, that say if Blank, take the next one.
But it haves to be the biggest accounts for the customer that deside the Division.

I have made an Index Column in the Query in the first table, hoping that I could do something, but I have not yet got it to work correctly:

Customer	value	branches
1	5.286.739	Pillow
2	5.286.789	Dogs
3	5.286.839	Cats

This is the Dax:

Division =

    CALCULATE(

        FIRSTNONBLANK(Table1[Division],1),

            FILTER(All(

                Table1),

                Table1[Date] = Table2[Date]

                    && Table1[Customer] = Table2[Customer]

                    && Table1[Division] <> ""))

Hoping for help :O)

[MaleneL · Accepted solution]

I ended up with this solution, where _Unsb = Divison, Enga_kredit = Value, Hovedkunde = Customer 

thanks for all the help

VAR _Unsb = Fact_Hovedkunder[ft_unsb_]

VAR _EngaKredit =

        CALCULATE(MAX(Fact_DWH[Enga_kredit]),

        FILTER(

            ALL(Fact_DWH),

            Fact_DWH[merkur_hovedkunde] = Fact_Hovedkunder[merkur_hovedkunde]

            && Fact_DWH[FBK_Date#Int#EndOfMonth] = Fact_Hovedkunder[FBK_Date#Int#EndOfMonth]

            && Fact_DWH[ft_unsb] <> BLANK()

        )

        )

VAR _NyUnsb =

        CALCULATE(

            FIRSTNONBLANK(Fact_DWH[ft_unsb],1),

            FILTER(

                ALL(Fact_DWH),

                Fact_DWH[merkur_hovedkunde] = Fact_Hovedkunder[merkur_hovedkunde]

                && Fact_DWH[FBK_Date#Int#EndOfMonth] = Fact_Hovedkunder[FBK_Date#Int#EndOfMonth]

                && Fact_DWH[ft_unsb] <> BLANK()

                && Fact_DWH[Enga_kredit] = _EngaKredit

            )

        )

VAR _Calculation =

IF(ISBLANK(_NyUnsb), "3", _NyUnsb)

RETURN

SWITCH(

    TRUE(),

    _Unsb <> BLANK(), _Unsb,

    _Unsb = BLANK(), _Calculation,

    "3")

[MaleneL]

Hi

Unfortunately, I was wrong regarding the first formular it doesn’t work as I hoped it would 😯
I couldn’t get the second one to work, so I switch back to the first one, but when I took a closer look, I could see it didn’t work.

I think I have read what the problem is (but not sure). The DAX is saying take the first Division for the biggest value that is not blank, but not the other way around, take the division for the highest value where the Division is not blank…

So, If I have the highest value where the division is blank it will return blank and not go to the next the combination highest value with a division…

[Anonymous]

Hi, @MaleneL 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it hasn't been resolved yet, please share more details about the issue you're having and we'll do our best to help you solve the problem you're having.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MaleneL]

will check Friday - I am on my way to a meeting and tomorrow out of the bulding alle day

:O)

[Bibiano_Geraldo]

Cool, take your time!

[Bibiano_Geraldo]

Hi @MaleneL ,

Please try this corrected DAX and let me nkow if it works:

Division =
VAR LargestValue =
    CALCULATE(
        MAX(Table1[Value]),
        FILTER(
            ALL(Table1),
            Table1[Customer] = Table2[Customer]
                && Table1[Date] = Table2[Date]
        )
    )
VAR DivisionForLargestValue =
    CALCULATE(
        FIRSTNONBLANK(Table1[Division], 1),
        FILTER(
            ALL(Table1),
            Table1[Customer] = Table2[Customer]
                && Table1[Date] = Table2[Date]
                && Table1[Value] = LargestValue
        )
    )
RETURN
    DivisionForLargestValue

[MaleneL]

If I put in 
&& Division <> BLANK () in both VAR then it is doing it right 

[Bibiano_Geraldo]

So it works?

[MaleneL]

Yes the first one you send me If you at && table1[Division <> BLANK()
in both VAr statemsnt 
:O)

I haven't had time to see if i can find the soluition to number 2 :O)

[Anonymous]

Hi, @MaleneL 

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bibiano_Geraldo]

Happy your made adjustments and its works for you.

I believe that the logic will be the same for second DAX as well

[MaleneL]

Hi Bibiano
Thanks! ;O)
I think it work, but I then realized another problem that I do hope you can help me with also :O)

I do have some customer that only have one account or are new. Is it possible to build something saying if no other account se last month and if no one at all then “NA”.

I am crossing my fingers :O)

[Bibiano_Geraldo]

Hi @MaleneL ,

Please update the Dax with the following formula and let me know if it works:

Division =
VAR LargestValue_CurrentMonth =
    CALCULATE(
        MAX(Table1[Value]),
        FILTER(
            ALL(Table1),
            Table1[Customer] = Table2[Customer]
                && Table1[Date] = Table2[Date]
        )
    )
VAR Division_CurrentMonth =
    CALCULATE(
        FIRSTNONBLANK(Table1[Division], 1),
        FILTER(
            ALL(Table1),
            Table1[Customer] = Table2[Customer]
                && Table1[Date] = Table2[Date]
                && Table1[Value] = LargestValue_CurrentMonth
        )
    )
VAR LargestValue_LastMonth =
    CALCULATE(
        MAX(Table1[Value]),
        FILTER(
            ALL(Table1),
            Table1[Customer] = Table2[Customer]
                && Table1[Date] = EDATE(Table2[Date], -1) -- Previous month
        )
    )
VAR Division_LastMonth =
    CALCULATE(
        FIRSTNONBLANK(Table1[Division], 1),
        FILTER(
            ALL(Table1),
            Table1[Customer] = Table2[Customer]
                && Table1[Date] = EDATE(Table2[Date], -1)
                && Table1[Value] = LargestValue_LastMonth
        )
    )
RETURN
    IF(
        NOT ISBLANK(Division_CurrentMonth),
        Division_CurrentMonth,
        IF(
            NOT ISBLANK(Division_LastMonth),
            Division_LastMonth,
            "NA"
        )
    )