How can I show two dimensions from two different tables that can't be related

[Syndicate_Admin]

Hello Group,

I'm working on an influx of people report that tells me the people who enter the store and those who buy and I'm making a bar chart and I want to put :

X-axis = Time of day

Y-axis = distinct count encargos_id

Y-axis = count of customers who have entered

I have a board that is Alfuencia, which has this style:

On the other hand, I have the Assignments table that would have all the information of the Assignments with their respective ids.

The connection between the two tables is through the Maste Calendar table:

Then what I'm trying to do is a bar chart like this:

As you can see, the recuentos_id bar has the same values when it shouldn't be, it should have different ones, and that's because of the table relationships, since the Flow table repeats dates for the same store, and the model conflicts, or that's what I think.

I would like you to help me visualize what I need.

Thank you very much in advance

 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Syndicate_Admin]

Hello again,

Here I make a numerical explanation, where it could be seen much better.

Influx Table:

shop	DIA period	date	Tickets
x1	Tomorrow	21/09/2024	25
x1	Late	21/09/2024	63
x2	Tomorrow	21/09/2024	25
x2	Late	21/09/2024	78
x3	Tomorrow	21/09/2024	96
x3	Late	21/09/2024	15
x4	Tomorrow	21/09/2024	89
x4	Late	21/09/2024	56
x5	Tomorrow	21/09/2024	232
x5	Late	21/09/2024	100

Table of Orders:

shop	encardos_id	DIA period	date
x1	1	Tomorrow	21/09/2024
x1	2	Late	21/09/2024
x2	3	Tomorrow	21/09/2024
x2	14	Tomorrow	21/09/2024
x2	4	Late	21/09/2024
x3	5	Tomorrow	21/09/2024
x3	6	Late	21/09/2024
x3	11	Late	21/09/2024
x4	12	Tomorrow	21/09/2024
x4	7	Tomorrow	21/09/2024
x4	8	Late	21/09/2024
x5	9	Tomorrow	21/09/2024
x5	10	Late	21/09/2024
x5	13	Tomorrow	21/09/2024

Those are the two tables that I have the data to deal with, those two tables are joined to a Master Calendar.

1. In the master calendar I can't add Morning and Afternoon as the fields would be repeated

2. I cannot join these two tables, since there would not be a 1 : 1 relationship, but many : many.

On the other hand, what I would like to obtain in the form of a table would be:

Dia period	encargos_id count	Sum of tickets
Tomorrow	7	25+25+96+89+232
Late	6	63+78+15+56+100

[Anonymous]

Thanks for lbendlin's concern  about this issue.

Hi, @Syndicate_Admin 

I am glad to help you.

Based on the data you have given for testing, I will give two methods below which I hope will help you.

First method:

Create two Measure:

SumOfTickets = SUM('Influx Table'[Tickets])

EncargosIDCount = 
VAR _currentrowDIA = SELECTEDVALUE('Influx Table'[DIA period])
RETURN COUNTROWS(FILTER('Orders Table','Orders Table'[DIA period]=_currentrowDIA))

Then drag the two Measure created to the corresponding visual:

The second method:

Directly New Table:

SummaryTable = 
SUMMARIZE(
    'Influx Table',
    'Influx Table'[DIA period],
    "encargos_id count", 
        VAR DiaPeriod = 'Influx Table'[DIA period]
        RETURN
        COUNTROWS(FILTER('Orders Table','Orders Table'[DIA period]=DiaPeriod)),
    "Sum of tickets", 
        CALCULATE(SUM('Influx Table'[Tickets]))
)

Then drag the corresponding fields to the visual:

I have attached the pbix file below, hope it helps!

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.

[Syndicate_Admin]

Hello good morning,

Thank you very much for the contribution.

To the last table created, could you add the date ?

SummaryTable =

SUMMARIZE(

    'Influx Table',

    'Influx Table'[DIA period],

    "encargos_id count", 

        VAR DiaPeriod = 'Influx Table'[DIA period]

        RETURN

        COUNTROWS(FILTER('Orders Table','Orders Table'[DIA period]=DiaPeriod)),

    "Sum of tickets", 

        CALCULATE(SUM('Influx Table'[Tickets]))

)

Because the date, as I've shown is 09/21/2024, but now you have every table as well
22/09/2024

23/09/2024

24/09/2024

Greetings and thank you very much

[Anonymous · Accepted solution]

Hi, @Syndicate_Admin 

Do you mean to add 'Influx Table'[date] to this virtual table? I don't know if I understood it correctly.

SummaryTable = 
SUMMARIZE(
    'Influx Table',
    'Influx Table'[date],
    'Influx Table'[DIA period],
    "encargos_id count", 
        VAR DiaPeriod = 'Influx Table'[DIA period]
        RETURN
        COUNTROWS(FILTER('Orders Table','Orders Table'[DIA period]=DiaPeriod)),
    "Sum of tickets", 
        CALCULATE(SUM('Influx Table'[Tickets]))
)

Please describe your problem in more detail, and it would be nice if you could provide some simple test data that doesn't contain sensitive information, as well as a screenshot of the results you are expecting.

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.

[lbendlin]

Is "Late"  equivalent to "Today"  ?

[Syndicate_Admin]

Afternoon = Between 12 PM and 12 AM (12:00 - 24:00)

Morning = Between 12 AM and 12 PM ( 00:00 - 12:00 )

[lbendlin]

Your sample data does not have time components.

[Syndicate_Admin]

I've given an example with the type of data that is in my table, that goes daily,
Influx:
changing the day, but always keeping in a series, the stores, period of time, the only thing that increases is the date and the number of enters.
Orders:

The number of stores is static, what increases is the encargos_id, date.

I don't know if I answer your question

[lbendlin]

No, sorry, not following at all. Hopefully someone else can help you further.